const express = require('express');
const book = express.Router();
const connection = require("../config");

book.post('/book/list', (req, res) => {
    //查询语句
    const {name, id,page, pageNo} = req.body;
    let sql = `select * from books where 1=1`
    if (name) sql += ` and name like '%${name}%' `
    if (id) sql += ` and id=${id}`
    // res.send(req.body)
    console.log(sql)
    connection.query(`${sql} limit ${(page - 1) * pageNo},${pageNo}`, function (err, result) {
        if (err) {
            console.log('[SELECT ERROR]:', err.message);
        }
        if(result.length) {
            result.forEach(item => {
                let time = new Date(item.time).toLocaleString()
                item.time = time.replace(/\//g, "-") 
            });
        }
        console.log(result,0)
        res.send({data:result, status: result.length ? 1 : 0}) //数据库查询结果返回到result中,把查询数据发送到客户端
    });
})

book.post("/book/delete", (req,res) => {
    let {id} = req.body;
    console.log(req.body)
    connection.query("delete from books where id=?", [id], (err, result) => {
        if (err) {
            return "刪除失敗"
        }
        console.log(result);
        res.send({status: 1, msg: '刪除成功'});
    })
})

book.post('/book/edit', (req, res) => {
    const {id, name, time} = req.body;
    connection.query("update books set name=?, time=? where id=?",[name,new Date(Date.parse(time)),id],(err,result)=>{
        if(err){
            res.send({status: 1, msg: '編輯失敗'});
            // return "失败";
        }
        console.log(result);
        res.send({status: 1, msg: '編輯成功'});
    })
})

book.post('/book/add', (req, res) => {
    const {name, id} = req.body;
    connection.query("insert into books(id,name,time) values(?,?,?)",[id,name,new Date()],(err,result)=>{
        console.log(err)
        if(err){
            res.send({status: 0, msg: "數據插入失敗"})
            return "失败";
        }
        console.log(result);
        res.send({status: 1, msg: '數據添加成功'});
    })
})

module.exports = book;